import pandas as pd
import numpy as np
from pandasql import sqldf
from datetime import datetime
from dateutil import parser
import statsmodels.api as sm
import sqlite3

table2_array = pd.Series(np.load('table2_array.npy'))
table3_array = pd.Series(np.load('table3_array.npy'))

def policy_3(input1, input2, input3):
    a = round(input1)
    b = round(input2)
    c = round(input3)
    return table3_array[25*(a-1)+5*(b-1)+(c-1)].values
def policy_2(input1, input2):
    a = round(input1)
    b = round(input2)
    return table2_array[5*(a-1) + (b-1)].values


dt0 = pd.read_stata("../OriginalData/hes_all_models.dta")
dt0['usid'] = dt0['corps'] * 100000000 + dt0['prov']*1000000 + dt0['dist']*10000 + dt0['vilg']*100 + dt0['ham']
dt_score = dt0.iloc[:, -32:]
dt_score['date'] = dt0['date']
dt = dt_score.dropna(axis=0)
dt.loc[:,'mod4'] = dt.loc[:,'mod4'].apply(lambda x: 70-ord(x))
dt = dt.loc[(dt['mod4']>0) & (dt['mod4']<5.01),:]
# 1a+1b = 2a
dt.loc[:,'2a'] = policy_2(dt['mod1a_num'],dt['mod1b_num'])
# 1e+1d+(1f+1g) = 2b
dt['1f1g'] = policy_2(dt['mod1f_num'],dt['mod1g_num'])
dt.loc[:,'2b'] = policy_3(dt['mod1e_num'],dt['mod1d_num'],dt['1f1g'])
# 1h+1i = 2c
dt.loc[:,'2c'] = policy_2(dt['mod1h_num'],dt['mod1i_num'])
# 1j+(1l+1k)+1g = 2d
dt.loc[:,'1l1k'] = policy_2(dt['mod1l_num'], dt['mod1k_num'])
dt.loc[:,'2d'] = policy_3(dt['mod1j_num'],dt['1l1k'],dt['mod1g_num'])
# 1o_1n+1p = 2e
dt.loc[:,'2e'] = policy_3(dt['mod1o_num'],dt['mod1n_num'],dt['mod1p_num'])
# 1r_1q+1s = 2f
dt.loc[:,'2f'] = policy_3(dt['mod1r_num'],dt['mod1q_num'],dt['mod1s_num'])
# 2a+2b+1c = 3a
dt.loc[:,'3a'] = policy_3(dt['2a'],dt['2b'],dt['mod1c_num'])
# 2d+2c+1m = 3b
dt.loc[:,'3b'] = policy_3(dt['2d'],dt['2c'],dt['mod1m_num'])
# 2e+2f = 3c
dt.loc[:,'3c'] = policy_2(dt['2e'], dt['2f'])
# 3a+3b+3c = 4
dt.loc[:,'my4'] = policy_3(dt['3a'],dt['3b'],dt['3c'])

# Sanity Check
(dt['mod4']!=dt['my4']).sum()
# 8 observations that lies in the border

lca_q_all = pd.read_stata("../OriginalData/lca_q_all.dta")
vmc_prepped = pd.read_stata("../OriginalData/vmc_prepped.dta")
bomb_1 = vmc_prepped.loc[:,['qdate','usid','fr_strikes_mean']]
bomb_1.loc[bomb_1.fr_strikes_mean>0, 'fr_strikes_mean'] = 1 # change the numric bombing scores in 0 and 1
bomb_1 = bomb_1.loc[bomb_1.isnull().sum(axis=1)==0,:]
bomb_1.fr_strikes_mean = bomb_1.fr_strikes_mean.astype('int')
bomb_1 = bomb_1.set_index('usid')
lca_1 = lca_q_all.loc[:,['qdate','usid','educ_c','sec_c','econ_c','admin_c','health_c','soccap_c']]
lca_1.iloc[:,1:]= lca_1.iloc[:,1:].apply(lambda x: round(x))
lca_1 = lca_1.loc[lca_1.isnull().sum(axis=1)==0,:]
lca_1 = lca_1.set_index('usid')

DB = sqlite3.connect('alldata.db')
bomb_1.to_sql('bomb_1',con=DB,if_exists='replace' )
lca_1.to_sql('lca_1', con=DB,if_exists='replace')
dt.to_sql('HES',con=DB,if_exists = 'replace' )
DB.close()

